package com.yeren.servlet.tarena.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.yeren.servlet.tarena.entity.Employee;


/*
 * Employee数据操作对象，负责Employee的增删改查
 */
public class EmployeeDAO {
	/*
	 * 查询所有员工
	 */
	public List<Employee> findAll() throws Exception{
		List<Employee> emps=new ArrayList<Employee>();
		Connection conn=null;
		PreparedStatement stmt=null;
		ResultSet rs=null;
		try{
			conn=DBUtil.getConnection();
			stmt=conn.prepareStatement("select * from liu_temp");
			rs=stmt.executeQuery();
			while(rs.next()){
				Employee emp=new Employee(
						rs.getInt("id"),
						rs.getString("name"),
						rs.getDouble("salary"),
						rs.getInt("age")
						);
				emps.add(emp);
			}
		}catch(Exception e){
			e.printStackTrace();
			throw e;
		}finally{
			DBUtil.close(conn);
		}
		return emps;
	}
	/*
	 * 删除员工信息
	 */
	public void delete(int id) throws Exception{
		Connection conn=null;
		PreparedStatement stmt=null;
		try{
			conn=DBUtil.getConnection();
			stmt=conn.prepareStatement("delete from liu_temp where id=?");
			stmt.setInt(1,id);
			stmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
			throw e;
		}finally{
			DBUtil.close(conn);
		}
	}
	/*
	 * 增加员工信息
	 */
	public void save(Employee emp) throws Exception{
		Connection conn=null;
		PreparedStatement stmt=null;
		try{
			conn=DBUtil.getConnection();
			stmt=conn.prepareStatement("insert into liu_temp values(emp_id_seq.nextval,?,?,?)");
			stmt.setString(1,emp.getName());
			stmt.setDouble(2,emp.getSalary());
			stmt.setInt(3,emp.getAge());
			stmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
			throw e;
		}finally{
			DBUtil.close(conn);
		}
	}
	/*
	 * 根据ID查询员工信息
	 */
	public Employee findById(int id) throws Exception{
		Employee emp=null;
		Connection conn=null;
		PreparedStatement stmt=null;
		ResultSet rs=null;
		try{
			conn=DBUtil.getConnection();
			stmt=conn.prepareStatement("select * from liu_temp where id=?");
			stmt.setInt(1, id);
			rs=stmt.executeQuery();
			if(rs.next()){
				emp=new Employee(
						rs.getInt("id"),
						rs.getString("name"),
						rs.getDouble("salary"),
						rs.getInt("age")
						);
			}
		}catch(Exception e){
			e.printStackTrace();
			throw e;
		}finally{
			DBUtil.close(conn);
		}
		return emp;
	}
	/*
	 * 保存员工修改信息
	 */
	public void modify(Employee emp)throws Exception{
		Connection conn=null;
		PreparedStatement stmt=null;
		try{
			conn=DBUtil.getConnection();
			stmt=conn.prepareStatement("update liu_temp set name=?,salary=?,age=? where id=?");
			stmt.setString(1,emp.getName());
			stmt.setDouble(2, emp.getSalary());
			stmt.setInt(3, emp.getAge());
			stmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
			throw e;
		}finally{
			DBUtil.close(conn);
		}
	}
}
